/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package SnpDbHandling;

import SnpDbEntities.Snippet;
import SnpDbEntities.User;
import SnpDbEntities.UserPermissions;
import SnpDbHndInterfaces.DBSnpHandling;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.logging.Level;

/**
 *
 * @author Łukasz 'byku' Guzik
 */
public class DBSnpHandler extends java.rmi.server.UnicastRemoteObject implements
	DBSnpHandling
{
    private final Connection dbCon;
    private final User connectedUser;

    public DBSnpHandler(Connection dbCon, User connectedUser) throws
	    RemoteException
    {
	this.dbCon = dbCon;
	this.connectedUser = connectedUser;
    }

    @Override
    public int addPrivateSnippet(Snippet newSnippet) throws RemoteException
    {
	int result = -1;
	SnippetRecord snpRecord = new SnippetRecord(newSnippet);

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "SELECT * FROM PrivateSnippets WHERE ID = -1;";
	    ResultSet rs = stmt.executeQuery(query);
	    rs.moveToInsertRow();
	    snpRecord.insertIntoResultSet(rs, true);
	    rs.insertRow();
	    rs.first();
	    result = rs.getInt("ID");
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return result;
	}
    }

    @Override
    public Snippet getPrivateSnippet(int id, boolean withData) throws
	    RemoteException
    {
	return getSnippet(false, id, withData);
    }

    @Override
    public LinkedHashMap<Integer, Snippet> getAllPrivateSnippets() throws
	    RemoteException
    {
	return getAllSnippets(false);
    }

    @Override
    public boolean deletePrivateSnippet(int id) throws RemoteException
    {
	boolean result = false;
	try
	{
	    dbCon.setAutoCommit(false);
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "DELETE FROM PrivateSnippets WHERE (ID = " + id
		    + ")AND(OwnerID = " + connectedUser.getId() + ");";

	    if(stmt.executeUpdate(query) == 1)
	    {
		result = true;
		dbCon.commit();
	    } else
	    {
		dbCon.rollback();
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    try
	    {
		dbCon.setAutoCommit(true);
	    } catch(SQLException e)
	    {
		DBHndLogger.logException(Level.SEVERE, e);
	    }
	    return result;
	}
    }

    @Override
    public boolean updatePrivateSnippet(Snippet updateSnippet, boolean withData)
	    throws RemoteException
    {
	return updateSnippet(updateSnippet, withData, false);
    }

    @Override
    public boolean updatePublicSnippet(Snippet updateSnippet, boolean withData)
	    throws RemoteException
    {
	return updateSnippet(updateSnippet, withData, true);
    }

    private boolean updateSnippet(Snippet updateSnippet, boolean withData, boolean isPublic)
    {
	if(isPublic && !connectedUser.canUpdatePublicSnippet())
	{
	    return false;
	}

	boolean result = false;
	SnippetRecord snpRecord = new SnippetRecord(updateSnippet);
	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query;
	    if(isPublic)
	    {
		query = "SELECT * FROM PublicSnippets WHERE ID = " + updateSnippet.getId() + ";";
	    } else
	    {
		query = "SELECT * FROM PrivateSnippets WHERE (ID = " + updateSnippet.getId() + ")AND(OwnerID = " + connectedUser.getId() + ");";
	    }
	    ResultSet rs = stmt.executeQuery(query);
	    if(rs.next())
	    {
		snpRecord.insertIntoResultSet(rs, withData);
		rs.updateRow();
		result = true;
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return result;
	}
    }

    @Override
    public Snippet getPublicSnippet(int id, boolean withData) throws
	    RemoteException
    {
	return getSnippet(true, id, withData);
    }

    @Override
    public LinkedHashMap<Integer, Snippet> getAllPublicSnippets() throws
	    RemoteException
    {
	return getAllSnippets(true);
    }

    @Override
    public boolean addToPublishQueue(int privateSnippetId, int publicCategoryId)
	    throws RemoteException
    {
	boolean result = false;
	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "INSERT INTO PublishQueue (PrivateSnippetID, PublicCategoryID) "
		    + "VALUES (" + privateSnippetId + ", " + publicCategoryId + ");";
	    if(stmt.executeUpdate(query) == 1)
	    {
		result = true;
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return result;
	}
    }

    @Override
    public boolean removeFromPublishQueue(int id) throws RemoteException
    {
	boolean result = false;

	try
	{
	    dbCon.setAutoCommit(false);

	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String deleteQuery = "DELETE FROM PublishQueue WHERE PrivateSnippetID = " + id + ";";
	    String getInfoQuery = "SELECT DISTINCT PublishQueue.PrivateSnippetID FROM PublishQueue ";
	    if(connectedUser.getPermissions() == UserPermissions.SUPER_ADMIN
		    || connectedUser.getPermissions() == UserPermissions.ADMIN)
	    {
		getInfoQuery += "WHERE PublishQueue.PrivateSnippetID = " + id + ";";
	    } else if(connectedUser.getPermissions() == UserPermissions.MODERATOR)
	    {
		getInfoQuery += "INNER JOIN ModeratorPublicCategory "
			+ "ON PublishQueue.PublicCategoryID = ModeratorPublicCategory.PublicCategoryID "
			+ "INNER JOIN PrivateSnippets ON PublishQueue.PrivateSnippetID = PrivateSnippets.ID "
			+ "WHERE (ModeratorPublicCategory.ModeratorID = " + connectedUser.getId()
			+ " OR PrivateSnippets.OwnerID = " + connectedUser.getId() + ") "
			+ "AND(PublishQueue.PrivateSnippetID = " + id + ");";
	    } else
	    {
		getInfoQuery += "INNER JOIN PrivateSnippets "
			+ "ON PublishQueue.PrivateSnippetID = PrivateSnippets.ID "
			+ "WHERE (PublishQueue.PrivateSnippetID = " + id
			+ ")AND(PrivateSnippets.OwnerID = " + connectedUser.getId() + ");";
	    }

	    ResultSet rs = stmt.executeQuery(getInfoQuery);
	    if(rs.next())
	    {
		if(stmt.executeUpdate(deleteQuery) == 1)
		{
		    dbCon.commit();
		    result = true;
		} else
		{
		    dbCon.rollback();
		}
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    try
	    {
		dbCon.setAutoCommit(true);
	    } catch(SQLException e)
	    {
		DBHndLogger.logException(Level.SEVERE, e);
	    }
	    return result;
	}
    }

    @Override
    public LinkedHashMap<Integer, Snippet> getSnippetsToPublish() throws
	    RemoteException
    {
	LinkedHashMap<Integer, Snippet> resultMap = new LinkedHashMap<Integer, Snippet>();
	Snippet tmpSnp;

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	    String query = "SELECT * FROM PublishQueue "
		    + "INNER JOIN PrivateSnippets ON PrivateSnippets.ID = PublishQueue.PrivateSnippetID";
	    if(connectedUser.getPermissions() == UserPermissions.SUPER_ADMIN
		    || connectedUser.getPermissions() == UserPermissions.ADMIN)
	    {
		query += ";";
	    } else if(connectedUser.getPermissions() == UserPermissions.MODERATOR)
	    {
		query += " INNER JOIN ModeratorPublicCategory ON "
			+ "PublishQueue.PublicCategoryID = ModeratorPublicCategory.PublicCategoryID "
			+ "WHERE ModeratorPublicCategory.ModeratorID = " + connectedUser.getId() + ";";
	    } else
	    {
		return null;
	    }

	    ResultSet rs = stmt.executeQuery(query);
	    while(rs.next())
	    {
		tmpSnp = SnippetRecord.getFromResultSet(rs, false).getSnippet();
		tmpSnp.setCategoryId(rs.getInt("PublicCategoryID"));
		resultMap.put(tmpSnp.getId(), tmpSnp);
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return resultMap;
	}
    }

    @Override
    public boolean publish(int id) throws RemoteException
    {
	boolean result = false;
	Snippet snippetToPublish;
	try
	{
	    dbCon.setAutoCommit(false);
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query = "SELECT * FROM PublishQueue "
		    + "INNER JOIN PrivateSnippets ON PrivateSnippets.ID = PublishQueue.PrivateSnippetID";
	    if(connectedUser.getPermissions() == UserPermissions.SUPER_ADMIN
		    || connectedUser.getPermissions() == UserPermissions.ADMIN)
	    {
		query += " WHERE PrivateSnippets.ID = " + id + ";";
	    } else if(connectedUser.getPermissions() == UserPermissions.MODERATOR)
	    {
		query += " INNER JOIN ModeratorPublicCategory ON "
			+ "PublishQueue.PublicCategoryID = ModeratorPublicCategory.PublicCategoryID "
			+ "WHERE (ModeratorPublicCategory.ModeratorID = " + connectedUser.getId()
			+ ")AND(PrivateSnippets.ID = " + id + ");";
	    } else
	    {
		return result;
	    }

	    ResultSet rs = stmt.executeQuery(query);
	    if(rs.next())
	    {
		snippetToPublish = SnippetRecord.getFromResultSet(rs, true).getSnippet();
		snippetToPublish.setCategoryId(rs.getInt("PublicCategoryID"));
		rs.close();

		query = "SET IDENTITY_INSERT PublicSnippets ON;";
		stmt.execute(query);

		query = "SELECT * FROM PublicSnippets WHERE ID = " + id + ";";
		rs = stmt.executeQuery(query);
		if(rs.next())
		{
		    rs.deleteRow();
		}
		rs.moveToInsertRow();
		rs.updateInt("ID", snippetToPublish.getId());
		SnippetRecord snpRec = new SnippetRecord(snippetToPublish);
		snpRec.insertIntoResultSet(rs, true);
		rs.insertRow();

		query = "SET IDENTITY_INSERT PublicSnippets OFF;";
		stmt.execute(query);
	    }

	    if(removeFromPublishQueue(id))
	    {
		dbCon.commit();
	    } else
	    {
		dbCon.rollback();
	    }
	    stmt.close();
	    result = true;
	} catch(SQLException e)
	{
	    dbCon.rollback();
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    try
	    {
		dbCon.setAutoCommit(true);
	    } catch(SQLException e)
	    {
		DBHndLogger.logException(Level.SEVERE, e);
	    }
	    return result;
	}
    }

    @Override
    public boolean unpublish(int id) throws RemoteException
    {
	boolean result = false;
	if(connectedUser.getPermissions() == UserPermissions.NORMAL_USER)
	{
	    return false;
	}

	try
	{
	    dbCon.setAutoCommit(false);
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    String query;

	    if(connectedUser.getPermissions() == UserPermissions.MODERATOR)
	    {
		query = "SELECT * FROM ModeratorPublicCategory INNER JOIN PublicSnippets "
			+ "ON PublicSnippets.CategoryID = ModeratorPublicCategory.PublicCategoryID "
			+ "WHERE (PublicSnippets.ID = " + id + ")AND("
			+ "ModeratorPublicCategory.ModeratorID = " + connectedUser.getId() + ");";

		ResultSet rs = stmt.executeQuery(query);
		if(!rs.next())
		{
		    stmt.close();
		    return false;
		} else
		{
		    rs.close();
		}
	    }

	    query = "DELETE FROM PublicSnippets WHERE ID = " + id + ";";
	    if(stmt.executeUpdate(query) == 1)
	    {
		dbCon.commit();
		result = true;
	    } else
	    {
		dbCon.rollback();
	    }

	} catch(SQLException e)
	{
	    dbCon.rollback();
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    try
	    {
		dbCon.setAutoCommit(true);
	    } catch(SQLException e)
	    {
		DBHndLogger.logException(Level.SEVERE, e);
	    }
	    return result;
	}
    }

    private Snippet getSnippet(boolean isPublic, int id, boolean withData)
    {
	SnippetRecord snpRecord = null;
	String sourceTable;

	if(isPublic)
	{
	    sourceTable = "PublicSnippets";
	} else
	{
	    sourceTable = "PrivateSnippets";
	}

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	    String query = "SELECT * FROM " + sourceTable + " WHERE ID = " + id + ";";
	    ResultSet rs = stmt.executeQuery(query);
	    if(rs.next())
	    {
		snpRecord = SnippetRecord.getFromResultSet(rs, withData);
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return snpRecord != null ? snpRecord.getSnippet() : null;
	}
    }

    private LinkedHashMap<Integer, Snippet> getAllSnippets(boolean isPublic)
    {
	LinkedHashMap<Integer, Snippet> resultMap = new LinkedHashMap<Integer, Snippet>();
	Snippet tmpSnp;

	try
	{
	    Statement stmt = dbCon.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	    String query = "SELECT * FROM ";
	    if(isPublic)
	    {
		query += "PublicSnippets;";
	    } else
	    {
		query += "PrivateSnippets WHERE OwnerID = " + connectedUser.getId() + ";";
	    }
	    ResultSet rs = stmt.executeQuery(query);
	    while(rs.next())
	    {
		tmpSnp = SnippetRecord.getFromResultSet(rs, false).getSnippet();
		resultMap.put(tmpSnp.getId(), tmpSnp);
	    }
	    stmt.close();
	} catch(SQLException e)
	{
	    DBHndLogger.logException(Level.WARNING, e);
	} finally
	{
	    return resultMap;
	}
    }
}
